package com.mybatisplus.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.mybatisplus.entity.SignData;
import com.mybatisplus.entity.Student;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author yancheng
 * @date 2023/3/31
 * apiNote
 */
//继承MybatisPlus接口
@Repository
public interface StudentMapper extends BaseMapper<Student> {
    @Select("SELECT * FROM sign_rob")
    List<SignData> fromTables();

    @Select("SELECT password FROM student WHERE name=#{name};")
    List<Student> getPasswordByName(String name);

    @Select("SELECT EXISTS(SELECT 1 FROM student WHERE stuid=#{stuid} AND password = #{password} )")
    boolean checkUserExists(@Param("stuid") String stuid, @Param("password") String password);

    @Insert("INSERT INTO student (id,name,classes,gender,stuid,score,password) values(#{id},#{name},#{classes},#{gender},#{stuid},#{score},#{password})")
    void insertFromExcel(Student student);

    @Select("SELECT name FROM student WHERE stuid=#{stuid}")
    String selectNameByStuid(String stuid);

    @Select("SELECT * FROM student where stuid=#{stuid}")
    Student selectAllByStuid(String stuid);

    @Update("UPDATE student set score=#{score} where stuid=#{stuid}")
    void updateScoreByStuid(@Param("score") Integer score, @Param("stuid") String stuid);

    @Update("UPDATE student set password=#{password} where stuid=#{stuid}")
    void updatePasswordByStuid(@Param("stuid") String stuid, @Param("password") String password);

    //    插入已签到的表
    @Insert("INSERT INTO sign_rob(name,stuid,times) values(#{name},#{stuid},now())")
    boolean intoSignRob(@Param("name") String name, @Param("stuid") String stuid);

    //    判断sign_rob中是否已经存在数据
    @Select("SELECT name FROM sign_rob where stuid = #{stuid}")
    String isExist(@Param("stuid") String stuid);

    @Select("SELECT * FROM sign_rob where stuid=#{stuid}")
    List<SignData> selectSignData(@Param("stuid") String stuid);

    @Select("select name from sign_rob where times between DATE_SUB(now(),INTERVAL 4 HOUR) and now()")
    List<SignData> selectAllSignData();

    @Select("select student.name from student where not exists (select sign_rob.name from sign_rob where times between DATE_SUB(now(),INTERVAL 4 HOUR) and now() and sign_rob.stuid = student.stuid);")
    List<SignData> selectNotSigned();

    @Delete("truncate table sign_rob")
    void clean();

    @Select("SELECT * from student where stuid=#{stuid}")
    Student selectStudentById(@Param("stuid") String stuid);
}
